package com.xxl.job.executor.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.ConfigProperties;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorGrantryTransService;

/**
 * 门架计费扣款流水监视明细实现类
 * @author zz
 *
 */
@Service("MonitorGrantryTransService")
public class MonitorGrantryTransService implements IMonitorGrantryTransService {
	
	Connection connPre = null;
	
	@Override
	public void transfer() throws Exception {
		  XxlJobLogger.log("=====开始执行业务操作===");
	        final String recordName = "grantry_trans_monitor_etctu";  //TB_STATIS_MONITOR_ETCTU

	        try {
	            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
	            connPre = DataSourceUtils.getConnection(dataSourcePre);
	            connPre.setAutoCommit(false);

	            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
	            if (recordStr == null) {
	                throw new Exception("[异常]本次最大记录RECORDID为空");
	            }
	            XxlJobLogger.log("[普通]本次最大记录RECORDID为:" + recordStr);
	            String dbCurTime = DBUtils.getDBCurTime(connPre);
	            XxlJobLogger.log("[普通]本次同步截止时间为:" + dbCurTime);
	            //同步数据到监视表
	            syncGrantryRecord(connPre, recordStr, dbCurTime);
	            
	            //拆包跨省流水给部里
	            analyzePackageCenter(connPre);
	            //拆包本省流水给发行方
	            analyzePackageIssue(connPre);
	            //拆包部里返回的记账数据
	            analyzePackageCenterAcc(connPre);
	            //拆包发行方返回的记账数据
	            analyzePackageIssueAcc(connPre);
	            
	            Integer count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
	            if (count == null || count != 1) {
	                XxlJobLogger.log("[异常]更新记录时间异常");
	                throw new Exception("[异常]更新记录时间异常");
	            }
	            XxlJobLogger.log("[普通]更新记录时间为:" + dbCurTime);
	            
	            //删除旧数据
	            deleteOldData(connPre, dbCurTime);
	            
	            //拆包待发送表
	            analyzePackageNoSend(connPre);
	            
	            connPre.commit();			 

	           
	            XxlJobLogger.log("=====完成执行业务操作===");
	            
	            String sqlPro = "truncate table TB_SYNC_MONITOR_ETCTU_TEMP";
				DBUtils.execute(connPre, sqlPro);
			    XxlJobLogger.log("[普通]重置临时表成功");		
	        }catch(Exception e){
	            if (connPre != null) {
	                try {
	                    connPre.rollback();
	                    XxlJobLogger.log("回滚成功.");
	                }catch (Exception e1){
	                    e1.printStackTrace();
	                }
	            }
	            XxlJobLogger.log("抛出异常："+e.getMessage());
	            throw e;
	        }finally{
	            if (connPre != null) {
	                try {
	                    connPre.setAutoCommit(true);
	                    connPre.close();
	                }catch (Exception e){
	                    e.printStackTrace();
	                }
	            }
	        }		
	}
	
	
	/**
	 * 同步发送失败的到统计表
	 * @param connPre
	 */
	private void analyzePackageNoSend(Connection connPre) {
		
	}
	
	/**
	 * 同步门架数据到监视表
	 * @param conn
	 * @param recordStr
	 * @param dbCurTime
	 * @throws Exception
	 */
	private  void syncGrantryRecord(Connection conn, String recordStr, String dbCurTime) throws Exception {
		String sql = " insert into TB_SYNC_MONITOR_ETCTU_TEMP(LIST_NO,TRADE_DATE,TABLE_NUM,RECV_STATE,QFSEND_STATE)"
				+ " select distinct t.tradeid,substr(t.tradeid,20,8),mod(to_number(substr(t.tradeid,20,8)),7),0,0 from grantry_trans_main_all t "
		        +"  where t.inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and t.inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
		Integer count = DBUtils.execute(conn, sql);
	    XxlJobLogger.log("[普通]本次从中台系统共同步所有数据(条):" + count);
	    
	    String updatetempsql = " MERGE INTO TB_SYNC_MONITOR_ETCTU_TEMP a "
	    		             + " USING ( select distinct tradeid from grantry_trans_main "
	    		             + " where inserttabletime>=to_date('"+recordStr+"','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('"+dbCurTime+"','yyyy-mm-dd hh24:mi:ss') "
	    		             + " ) b "
	    		             + " on (a.LIST_NO=b.tradeid)"
	    		             + " WHEN MATCHED THEN "
	    		             + " UPDATE SET RECV_STATE=1,QFSEND_STATE=1 ";
	    count = DBUtils.execute(conn, updatetempsql);
        XxlJobLogger.log("[普通]本次根据正常门架交易流水修改临时表接受状态和发送拆分状态(条):" + count);
        
        
        //从临时表同步到监视表
		/*
		 * PreparedStatement preparedStatement = null; String monitorNumSql =
		 * " select t.list_no,t.trade_date,t.recv_state,t.qfsend_state,t.noclear_state,t.clear_state from TB_SYNC_MONITOR_ETCTU_TEMP t  "
		 * ; preparedStatement = conn.prepareStatement(monitorNumSql); ResultSet rs =
		 * preparedStatement.executeQuery(); String tradeDate =null; String listNo =
		 * null; Statement st = conn.createStatement(); int index = 0;
		 * while(rs.next()){//判断是否还有下一行 listNo = rs.getString("list_no");//流水号 tradeDate
		 * = rs.getString("trade_date");
		 * 
		 * //计算把该流水数据插入监视表 index = index +1; Integer tradeNum =
		 * Integer.valueOf(tradeDate); int yuNum = tradeNum%7; String tableName =
		 * "TB_SYNC_MONITOR_ETCTU_"+yuNum; String sqlMonitor =
		 * " MERGE INTO "+tableName+" a " +
		 * " USING (select t.LIST_NO,t.TRADE_DATE,t.RECV_STATE,t.QFSEND_STATE,t.NOCLEAR_STATE,t.CLEAR_STATE from TB_SYNC_MONITOR_ETCTU_TEMP t  where t.LIST_NO='"
		 * +listNo+"') b " + " on ( a.LIST_NO = b.LIST_NO ) " + " WHEN MATCHED THEN " +
		 * " UPDATE SET a.trade_date = b.trade_date,a.recv_state =b.recv_state,a.qfsend_state=b.qfsend_state,a.noclear_state=b.noclear_state,a.clear_state = b.clear_state,a.UPDATE_TIME=sysdate  "
		 * + " WHEN NOT MATCHED THEN " +
		 * "  INSERT (a.LIST_NO,a.TRADE_DATE,a.RECV_STATE,a.QFSEND_STATE,a.NOCLEAR_STATE,a.CLEAR_STATE,a.INSERT_TIME,a.UPDATE_TIME) "
		 * +
		 * "  VALUES (b.LIST_NO,b.TRADE_DATE,b.RECV_STATE,b.QFSEND_STATE,b.NOCLEAR_STATE,b.CLEAR_STATE,sysdate,sysdate) "
		 * ; st.addBatch(sqlMonitor); if(index%1000==0) { st.executeBatch();
		 * st.clearBatch(); } } st.executeBatch(); st.clearBatch();
		 * 
		 * st.close();
		 * 
		 * rs.close(); preparedStatement.close();
		 * 
		 * XxlJobLogger.log("[普通]本次合并门架交易流水到监视表(条):" + index);
		 */
        //从临时表同步到监视表
        sysnorTempToTable(conn,0);
        sysnorTempToTable(conn,1);
        sysnorTempToTable(conn,2);
        sysnorTempToTable(conn,3);
        sysnorTempToTable(conn,4);
        sysnorTempToTable(conn,5);
        sysnorTempToTable(conn,6);
        
		
		//插入备份表
	   String bakSql = "insert into TB_SYNC_MONITOR_ETCTU_BAK(LIST_NO,TRADE_DATE,RECV_STATE,QFSEND_STATE,NOCLEAR_STATE,CLEAR_STATE,INSERT_TIME) " +
	            "select LIST_NO,TRADE_DATE,RECV_STATE,QFSEND_STATE,NOCLEAR_STATE,CLEAR_STATE,sysdate from TB_SYNC_MONITOR_ETCTU_TEMP ";
	    count = DBUtils.execute(conn, bakSql);
	    XxlJobLogger.log("[普通]本次备份门架交易流水(条):" + count);
	
		
		 sql = "delete from TB_SYNC_MONITOR_ETCTU_TEMP "; 
		 count = DBUtils.execute(conn, sql); 
		 XxlJobLogger.log("[普通]本次清空门架交易临时流水(条):" + count);		 
	}
	
	 void analyzePackageCenter(Connection conn) throws Exception {

	    }

    void analyzePackageIssue(Connection conn) throws Exception {

    }

    void analyzePackageCenterAcc(Connection conn) throws Exception {

    }

    void analyzePackageIssueAcc(Connection conn) throws Exception {

    }
    
   private void sysnorTempToTable(Connection conn,int num)throws Exception {
	  String sql = " MERGE INTO tb_sync_monitor_etctu_"+num+"  a " + 
       		"   using (  select t.LIST_NO,t.TRADE_DATE,t.RECV_STATE,t.QFSEND_STATE,t.NOCLEAR_STATE,t.CLEAR_STATE,t.table_num from TB_SYNC_MONITOR_ETCTU_TEMP t  where t.table_num="+num+"  " + 
       		"    ) b  " + 
       		"  on ( a.LIST_NO = b.LIST_NO ) " + 
       		"  WHEN MATCHED THEN " + 
       		"  UPDATE SET a.trade_date = b.trade_date,a.recv_state =b.recv_state,a.qfsend_state=b.qfsend_state,a.noclear_state=b.noclear_state,a.clear_state = b.clear_state,a.UPDATE_TIME=sysdate  " + 
       		"  WHEN NOT MATCHED THEN " + 
       		"  INSERT (a.LIST_NO,a.TRADE_DATE,a.RECV_STATE,a.QFSEND_STATE,a.NOCLEAR_STATE,a.CLEAR_STATE,a.INSERT_TIME,a.UPDATE_TIME) " + 
       		"  VALUES (b.LIST_NO,b.TRADE_DATE,b.RECV_STATE,b.QFSEND_STATE,b.NOCLEAR_STATE,b.CLEAR_STATE,sysdate,sysdate)  ";
      int  count = DBUtils.execute(conn, sql);
       XxlJobLogger.log("[普通]本次合并门架交易流水到监视"+num+"表(条):" + count);
   }
    
    /**
     * 删除几天前的旧数据
     * @param conn
     * @param dbCurTime
     * @throws Exception
     */
    void deleteOldData(Connection conn, String dbCurTime) throws Exception {
    	  //清除7天前的数据（配置文件中配置）
        String strHour = dbCurTime.substring(11,13);
        if (strHour.equals("02")) { //凌晨2点执行一次删除7日前的数据
        	 String strCurDay = dbCurTime.substring(0,10);
             final String recordDel = "del_tb_sync_monitor_etctu_num";
             String recordStrDel = DBUtils.getCurSynRecordNum(connPre, recordDel);
             XxlJobLogger.log("[普通]本次定时清除历史数据RECORDID为:" + recordStrDel);
             
             if (recordStrDel != null && strCurDay.compareTo(recordStrDel) > 0) {        
            	 ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
                 String dataDays = configProperties.getDataDays();         
            	 deleteMonitorDat(conn,dataDays);
                 
            	 Integer count = DBUtils.modifySynRecordNum(connPre, recordDel, recordStrDel, strCurDay);
               if (count == null || count != 1) {
                   XxlJobLogger.log("[异常]更新"+dataDays+"日前数据清除日期异常");
                   throw new Exception("[异常]更新"+dataDays+"日前数据清除日期异常");
               }
               XxlJobLogger.log("[普通]更新"+dataDays+"日前数据清除日期为:" + strCurDay);
             }
             
             //7天凌晨执行一次重新分析统计信息
             Integer nDay = Integer.parseInt(dbCurTime.substring(8,10));
             if (nDay % 7 == 1) {
                 int num = 0;
                 for(int i=0;i<7;i++) {
                	 String sqlPro = "analyze table tb_sync_monitor_etctu_"+i+" compute statistics ";
                	 Integer count = DBUtils.execute(connPre, sqlPro);
                	 num = num + count;                    
                 }
                 XxlJobLogger.log("[普通]门架监视表重新分析统计信息(条):"+num);
                
             }
        }
    }
    
    private void deleteMonitorDat(Connection conn,String dataDays) throws SQLException {
    	 
          int count = 0;
          for(int num=0;num<7;num++) { 
        	 String sqlPro = "delete from TB_SYNC_MONITOR_ETCTU_"+num+" where TRADE_DATE<to_char(sysdate-interval'"+dataDays+"'day, 'yyyy-mm-dd') ";
             Integer delNum = DBUtils.execute(connPre, sqlPro);
             count = count + delNum;
          }
          XxlJobLogger.log("[普通]本次删除异常明细表"+dataDays+"天前数据(条):"+count);
      }
	
	
	public static void main(String[] args) {
		int num = 2020511;
		int yunum = num%7;
		System.out.println("yunnum:"+yunum);
		
		 num = 2020512;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		 num = 2020513;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		 num = 2020514;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		 num = 2020515;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		 num = 2020516;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		 num = 2020517;
		 yunum = num%7;
		System.out.println("yunnum:"+yunum);
		
	}
	

}
